
<!doctype html>
<html lang="en" class="no-js">
  <head>
    
      <meta charset="utf-8">
      <meta name="viewport" content="width=device-width,initial-scale=1">
      
      
      
      <link rel="icon" href="../../../../static/images/favicon.png">
      <meta name="generator" content="mkdocs-1.3.0, mkdocs-material-8.2.8">
    
    
      
        <title>基于主机离线部署 MySQL MGR 高可用生产集群 - WL4G DOCS</title>
      
    
    
      <link rel="stylesheet" href="../../../../assets/stylesheets/main.644de097.min.css">
      
        
        <link rel="stylesheet" href="../../../../assets/stylesheets/palette.e6a45f82.min.css">
        
      
    
    
    
      
        
        
        <link rel="preconnect" href="https://fonts.gstatic.com" crossorigin>
        <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Roboto:300,300i,400,400i,700,700i%7CRoboto+Mono:400,400i,700,700i&display=fallback">
        <style>:root{--md-text-font:"Roboto";--md-code-font:"Roboto Mono"}</style>
      
    
    
      <link rel="stylesheet" href="../../../../static/css/util.css">
    
    <script>__md_scope=new URL("../../../..",location),__md_get=(e,_=localStorage,t=__md_scope)=>JSON.parse(_.getItem(t.pathname+"."+e)),__md_set=(e,_,t=localStorage,a=__md_scope)=>{try{t.setItem(a.pathname+"."+e,JSON.stringify(_))}catch(e){}}</script>
    
      

    
    
  </head>
  
  
    
    
      
    
    
    
    
    <body dir="ltr" data-md-color-scheme="default" data-md-color-primary="" data-md-color-accent="">
  
    
    
      <script>var palette=__md_get("__palette");if(palette&&"object"==typeof palette.color)for(var key of Object.keys(palette.color))document.body.setAttribute("data-md-color-"+key,palette.color[key])</script>
    
    <input class="md-toggle" data-md-toggle="drawer" type="checkbox" id="__drawer" autocomplete="off">
    <input class="md-toggle" data-md-toggle="search" type="checkbox" id="__search" autocomplete="off">
    <label class="md-overlay" for="__drawer"></label>
    <div data-md-component="skip">
      
        
        <a href="#mysql-mgr" class="md-skip">
          Skip to content
        </a>
      
    </div>
    <div data-md-component="announce">
      
    </div>
    
      <div data-md-component="outdated" hidden>
        <aside class="md-banner md-banner--warning">
          
        </aside>
      </div>
    
    
      

<header class="md-header" data-md-component="header">
  <nav class="md-header__inner md-grid" aria-label="Header">
    <a href="../../../.." title="WL4G DOCS" class="md-header__button md-logo" aria-label="WL4G DOCS" data-md-component="logo">
      
  <img src="../../../../static/images/mylogo.jpeg" alt="logo">

    </a>
    <label class="md-header__button md-icon" for="__drawer">
      <svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24"><path d="M3 6h18v2H3V6m0 5h18v2H3v-2m0 5h18v2H3v-2z"/></svg>
    </label>
    <div class="md-header__title" data-md-component="header-title">
      <div class="md-header__ellipsis">
        <div class="md-header__topic">
          <span class="md-ellipsis">
            WL4G DOCS
          </span>
        </div>
        <div class="md-header__topic" data-md-component="header-topic">
          <span class="md-ellipsis">
            
              基于主机离线部署 MySQL MGR 高可用生产集群
            
          </span>
        </div>
      </div>
    </div>
    
      <form class="md-header__option" data-md-component="palette">
        
          
          
          <input class="md-option" data-md-color-media="(prefers-color-scheme: light)" data-md-color-scheme="default" data-md-color-primary="" data-md-color-accent=""  aria-label="Switch to dark mode"  type="radio" name="__palette" id="__palette_1">
          
            <label class="md-header__button md-icon" title="Switch to dark mode" for="__palette_2" hidden>
              <svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24"><path d="M17 6H7c-3.31 0-6 2.69-6 6s2.69 6 6 6h10c3.31 0 6-2.69 6-6s-2.69-6-6-6zm0 10H7c-2.21 0-4-1.79-4-4s1.79-4 4-4h10c2.21 0 4 1.79 4 4s-1.79 4-4 4zM7 9c-1.66 0-3 1.34-3 3s1.34 3 3 3 3-1.34 3-3-1.34-3-3-3z"/></svg>
            </label>
          
        
          
          
          <input class="md-option" data-md-color-media="(prefers-color-scheme: dark)" data-md-color-scheme="slate" data-md-color-primary="" data-md-color-accent=""  aria-label="Switch to light mode"  type="radio" name="__palette" id="__palette_2">
          
            <label class="md-header__button md-icon" title="Switch to light mode" for="__palette_1" hidden>
              <svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24"><path d="M17 7H7a5 5 0 0 0-5 5 5 5 0 0 0 5 5h10a5 5 0 0 0 5-5 5 5 0 0 0-5-5m0 8a3 3 0 0 1-3-3 3 3 0 0 1 3-3 3 3 0 0 1 3 3 3 3 0 0 1-3 3z"/></svg>
            </label>
          
        
      </form>
    
    
      <div class="md-header__option">
        <div class="md-select">
          
          <button class="md-header__button md-icon" aria-label="Select language">
            <svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24"><path d="m12.87 15.07-2.54-2.51.03-.03A17.52 17.52 0 0 0 14.07 6H17V4h-7V2H8v2H1v2h11.17C11.5 7.92 10.44 9.75 9 11.35 8.07 10.32 7.3 9.19 6.69 8h-2c.73 1.63 1.73 3.17 2.98 4.56l-5.09 5.02L4 19l5-5 3.11 3.11.76-2.04M18.5 10h-2L12 22h2l1.12-3h4.75L21 22h2l-4.5-12m-2.62 7 1.62-4.33L19.12 17h-3.24z"/></svg>
          </button>
          <div class="md-select__inner">
            <ul class="md-select__list">
              
                <li class="md-select__item">
                  <a href="/en/" hreflang="en" class="md-select__link">
                    English
                  </a>
                </li>
                
                <li class="md-select__item">
                  <a href="/zh/" hreflang="zh" class="md-select__link">
                    简体中文
                  </a>
                </li>
                
            </ul>
          </div>
        </div>
      </div>
    
    
      <label class="md-header__button md-icon" for="__search">
        <svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24"><path d="M9.5 3A6.5 6.5 0 0 1 16 9.5c0 1.61-.59 3.09-1.56 4.23l.27.27h.79l5 5-1.5 1.5-5-5v-.79l-.27-.27A6.516 6.516 0 0 1 9.5 16 6.5 6.5 0 0 1 3 9.5 6.5 6.5 0 0 1 9.5 3m0 2C7 5 5 7 5 9.5S7 14 9.5 14 14 12 14 9.5 12 5 9.5 5z"/></svg>
      </label>
      <div class="md-search" data-md-component="search" role="dialog">
  <label class="md-search__overlay" for="__search"></label>
  <div class="md-search__inner" role="search">
    <form class="md-search__form" name="search">
      <input type="text" class="md-search__input" name="query" aria-label="Search" placeholder="Search" autocapitalize="off" autocorrect="off" autocomplete="off" spellcheck="false" data-md-component="search-query" required>
      <label class="md-search__icon md-icon" for="__search">
        <svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24"><path d="M9.5 3A6.5 6.5 0 0 1 16 9.5c0 1.61-.59 3.09-1.56 4.23l.27.27h.79l5 5-1.5 1.5-5-5v-.79l-.27-.27A6.516 6.516 0 0 1 9.5 16 6.5 6.5 0 0 1 3 9.5 6.5 6.5 0 0 1 9.5 3m0 2C7 5 5 7 5 9.5S7 14 9.5 14 14 12 14 9.5 12 5 9.5 5z"/></svg>
        <svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24"><path d="M20 11v2H8l5.5 5.5-1.42 1.42L4.16 12l7.92-7.92L13.5 5.5 8 11h12z"/></svg>
      </label>
      <nav class="md-search__options" aria-label="Search">
        
        <button type="reset" class="md-search__icon md-icon" aria-label="Clear" tabindex="-1">
          <svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 24 24"><path d="M19 6.41 17.59 5 12 10.59 6.41 5 5 6.41 10.59 12 5 17.59 6.41 19 12 13.41 17.59 19 19 17.59 13.41 12 19 6.41z"/></svg>
        </button>
      </nav>
      
        <div class="md-search__suggest" data-md-component="search-suggest"></div>
      
    </form>
    <div class="md-search__output">
      <div class="md-search__scrollwrap" data-md-scrollfix>
        <div class="md-search-result" data-md-component="search-result">
          <div class="md-search-result__meta">
            Initializing search
          </div>
          <ol class="md-search-result__list"></ol>
        </div>
      </div>
    </div>
  </div>
</div>
    
    
  </nav>
  
</header>
    
    <div class="md-container" data-md-component="container">
      
      
        
          
            
<nav class="md-tabs" aria-label="Tabs" data-md-component="tabs">
  <div class="md-tabs__inner md-grid">
    <ul class="md-tabs__list">
      
        
  
  


  
  
  
    <li class="md-tabs__item">
      <a href="../../../.." class="md-tabs__link">
        Getting Started
      </a>
    </li>
  

      
    </ul>
  </div>
</nav>
          
        
      
      <main class="md-main" data-md-component="main">
        <div class="md-main__inner md-grid">
          
            
              
              <div class="md-sidebar md-sidebar--primary" data-md-component="sidebar" data-md-type="navigation" >
                <div class="md-sidebar__scrollwrap">
                  <div class="md-sidebar__inner">
                    

  


  

<nav class="md-nav md-nav--primary md-nav--lifted md-nav--integrated" aria-label="Navigation" data-md-level="0">
  <label class="md-nav__title" for="__drawer">
    <a href="../../../.." title="WL4G DOCS" class="md-nav__button md-logo" aria-label="WL4G DOCS" data-md-component="logo">
      
  <img src="../../../../static/images/mylogo.jpeg" alt="logo">

    </a>
    WL4G DOCS
  </label>
  
  <ul class="md-nav__list" data-md-scrollfix>
    
      
      
      

  
  
  
    
    <li class="md-nav__item md-nav__item--nested">
      
      
        <input class="md-nav__toggle md-toggle" data-md-toggle="__nav_1" data-md-state="indeterminate" type="checkbox" id="__nav_1" checked>
      
      
      
      
        <label class="md-nav__link" for="__nav_1">
          Getting Started
          <span class="md-nav__icon md-icon"></span>
        </label>
      
      <nav class="md-nav" aria-label="Getting Started" data-md-level="1">
        <label class="md-nav__title" for="__nav_1">
          <span class="md-nav__icon md-icon"></span>
          Getting Started
        </label>
        <ul class="md-nav__list" data-md-scrollfix>
          
            
              
  
  
  
    <li class="md-nav__item">
      <a href="../../../.." class="md-nav__link">
        Introduction
      </a>
    </li>
  

            
          
            
              
  
  
  
    <li class="md-nav__item">
      <a href="../../../../ABOUT_CN/" class="md-nav__link">
        About
      </a>
    </li>
  

            
          
        </ul>
      </nav>
    </li>
  

    
  </ul>
</nav>
                  </div>
                </div>
              </div>
            
            
          
          <div class="md-content" data-md-component="content">
            <article class="md-content__inner md-typeset">
              
                


<h1 id="mysql-mgr">基于主机离线部署 MySQL MGR 高可用生产集群<a class="headerlink" href="#mysql-mgr" title="Permanent link">&para;</a></h1>
<h2 id="1">1. 部署准备<a class="headerlink" href="#1" title="Permanent link">&para;</a></h2>
<ul>
<li>
<p><a href="https://blogs.wl4g.com/archives/2477">另一篇: 基于纯 DOCKER 部署 MYSQL MGR 高可用集群</a></p>
</li>
<li>
<p><a href="https://dev.mysql.com/doc/refman/5.7/en/group-replication.html">官方文档: dev.mysql.com/doc/refman/5.7/en/group-replication.html</a></p>
</li>
<li>
<p>部署集群拓扑图</p>
</li>
</ul>
<table>
<thead>
<tr>
<th>IP</th>
<th>Description</th>
</tr>
</thead>
<tbody>
<tr>
<td>10.0.0.161</td>
<td>node1 (master)</td>
</tr>
<tr>
<td>10.0.0.162</td>
<td>node2 (slave)</td>
</tr>
<tr>
<td>10.0.0.163</td>
<td>node3 (slave)</td>
</tr>
</tbody>
</table>
<ul>
<li>
<p>下载安装包</p>
</li>
<li>
<p>一键下载 <code>(可复制直接执行)</code></p>
</li>
</ul>
<div class="highlight"><pre><span></span><code><a id="__codelineno-0-1" name="__codelineno-0-1"></a><a href="#__codelineno-0-1"><span class="linenos" data-linenos="1 "></span></a>sudo wget -O /tmp/mysql.tar.gz https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz
</code></pre></div>
<ul>
<li>
<p>手动下载
注: <a href="https://dev.mysql.com/downloads/mysql/">官方下载页:https://dev.mysql.com/downloads/mysql/</a> 如果需要登录就只能自行手动下载，请找对版本，推荐使用 <code>mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz</code></p>
</li>
<li>
<p>安装 <code>(可复制直接执行)</code></p>
</li>
</ul>
<div class="highlight"><pre><span></span><code><a id="__codelineno-1-1" name="__codelineno-1-1"></a><a href="#__codelineno-1-1"><span class="linenos" data-linenos="1 "></span></a>sudo mkdir -p /usr/lib/mysql-current
<a id="__codelineno-1-2" name="__codelineno-1-2"></a><a href="#__codelineno-1-2"><span class="linenos" data-linenos="2 "></span></a>sudo tar -xf /tmp/mysql.tar.gz --strip-components<span class="o">=</span><span class="m">1</span> -C /usr/lib/mysql-current
<a id="__codelineno-1-3" name="__codelineno-1-3"></a><a href="#__codelineno-1-3"><span class="linenos" data-linenos="3 "></span></a><span class="c1">#sudo rm -rf /tmp/mysql.tar.gz</span>
</code></pre></div>
<p>注: 监听地址每个节点不同 <code>loose-group_replication_local_address= "ip:3306"</code></p>
<h2 id="2">2. 环境配置 <code>(可复制直接执行)</code><a class="headerlink" href="#2" title="Permanent link">&para;</a></h2>
<ul>
<li>node1、node2、node3 分别执行</li>
</ul>
<p><details style="cursor:pointer;;padding-left:2%">
<summary style="color:#007acc;">[展开] /etc/profile.d/profile-mysqld.sh</summary>
<code><pre>
# 下载环境配置
sudo curl -sSL -o /etc/profile.d/profile-mysqld.sh https://gitee.com/wl4g/blogs/raw/master/docs/articles/database/hosts-mysql-mgr-deployment/resources/profile-mysqld.sh

# 使生效
. /etc/profile

# 创建用户、目录
sudo groupadd $MYSQL_GROUP
sudo useradd -g $MYSQL_GROUP $MYSQL_USER
sudo mkdir -p $MYSQL_DATA_DIR
sudo mkdir -p ${MYSQL_LOG_DIR}
# 授权
sudo chmod -R 755 ${MYSQL_HOME}
sudo chmod -R 700 ${MYSQL_DATA_DIR}
sudo chmod -R 755 ${MYSQL_LOG_DIR}
sudo chown -R ${MYSQL_USER}:${MYSQL_GROUP} ${MYSQL_HOME}
sudo chown -R ${MYSQL_USER}:${MYSQL_GROUP} ${MYSQL_DATA_DIR}
sudo chown -R ${MYSQL_USER}:${MYSQL_GROUP} ${MYSQL_LOG_DIR}
</pre></code>
</details></p>

<h2 id="3-3-ip">3. 运行配置 <code>(不可复制直接执行, 请注意生成 3 个节点的配置时的  ip 不同。)</code><a class="headerlink" href="#3-3-ip" title="Permanent link">&para;</a></h2>
<p><details style="cursor:pointer;;padding-left:2%">
<summary style="color:#007acc;">[展开] /etc/my.cnf</summary>
<code><pre>
# 根据主机配置生成调优的配置
totalMemKB=$(cat /proc/meminfo|grep MemTotal|sed -r 's|[^0-9]+([0-9]+).*$|\1|')
innodbBufferPoolSizeMB=$(($totalMemKB/1024*7/10)) # 按专有服务器计算为主机总内存的70%

# 下载配置模版
sudo curl -sSL -o /tmp/my.cnf.tpl https://gitee.com/wl4g/blogs/raw/master/docs/articles/database/hosts-mysql-mgr-deployment/resources/my.cnf.tpl

# 生成3个节点的配置（***按实际自行修改***）
export host1='10.0.0.161'
export host2='10.0.0.162'
export host3='10.0.0.163'
export seeds="$host1:23366,$host2:23366,$host3:23366"
sudo cat /tmp/my.cnf.tpl | sed "s/HOST/$host1/g" | sed 's/SERVER_ID/1/g' | sed "s/SEEDS/$seeds/g" > /tmp/my1.cnf
sudo cat /tmp/my.cnf.tpl | sed "s/HOST/$host2/g" | sed 's/SERVER_ID/2/g' | sed "s/SEEDS/$seeds/g" > /tmp/my2.cnf
sudo cat /tmp/my.cnf.tpl | sed "s/HOST/$host3/g" | sed 's/SERVER_ID/3/g' | sed "s/SEEDS/$seeds/g" > /tmp/my3.cnf

# 授权
sudo chmod -R 755 ${MYSQL_CONF_FILE}
sudo chown -R ${MYSQL_USER}:${MYSQL_GROUP} ${MYSQL_CONF_FILE}

# 拷贝到其他节点
sudo cp /tmp/my1.cnf /etc/my.cnf
sudo scp /tmp/my2.cnf $host2:/etc/my.cnf
sudo scp /tmp/my3.cnf $host3:/etc/my.cnf
</pre></code>
</details></p>

<h2 id="4">4. 初始化 <code>(可复制直接执行)</code><a class="headerlink" href="#4" title="Permanent link">&para;</a></h2>
<ul>
<li>node1、node2、node3 分别执行</li>
</ul>
<p><details style="cursor:pointer;;padding-left:2%">
<summary style="color:#007acc;">[展开] Initializating MySQL metadata</summary>
<code><pre>
. /etc/profile # 加载环境

# (推荐可选) 如果已启动先停掉(防止反复执行出错)
systemctl stop mysqld
# 删除否则初始化会失败. [为通用安全起见, 只执行mv]
trashTmpDir="/tmp/removed_mysql_data_$(date +%Y%m%d%H%M%S)"
mkdir -p $trashTmpDir
mv ${MYSQL_DATA_DIR}/* $trashTmpDir

# 切换用户
su $MYSQL_USER

# 加载环境
. /etc/profile

# 执行初始化
${MYSQL_HOME}/bin/mysqld \
--defaults-file=${MYSQL_CONF_FILE} \
--initialize \
--user=${MYSQL_USER} \
--basedir=${MYSQL_HOME} \
--datadir=${MYSQL_DATA_DIR} \
--plugin-dir=${MYSQL_PLUGIN_DIR} > "$MYSQL_LOG_ERR" 2>&1

# 【重要】查看日志，找到初始化生成的临时密码
cat ${MYSQL_LOG_ERR}

# 回到管理账户(加判断防止反复执行出错)
[ "$USER" == "$MYSQL_USER" ] && exit
</pre></code>
</details></p>

<h2 id="5">5. 服务配置 <code>(可复制直接执行)</code><a class="headerlink" href="#5" title="Permanent link">&para;</a></h2>
<ul>
<li>
<p>node1、node2、node3 分别执行</p>
</li>
<li>
<p>mysqld.sh</p>
</li>
</ul>
<p><details style="cursor:pointer;;padding-left:2%">
<summary style="color:#007acc;">[展开] /etc/init.d/mysqld.sh</summary>
<code><pre>
sudo curl -sSL -o /etc/init.d/mysqld.sh https://gitee.com/wl4g/blogs/raw/master/docs/articles/database/hosts-mysql-mgr-deployment/resources/mysqld.sh

# 授权
sudo chmod -R 755 /etc/init.d/mysqld.sh
sudo chown -R mysql:mysql /etc/init.d/mysqld.sh
</pre></code>
</details></p>

<ul>
<li>mysqld.service</li>
</ul>
<p><details style="cursor:pointer;;padding-left:2%">
<summary style="color:#007acc;">[展开] /etc/systemd/system/mysqld.service</summary>
<code><pre>
# 下载服务配置
sudo curl -sSL -o /etc/systemd/system/mysqld.service https://gitee.com/wl4g/blogs/raw/master/docs/articles/database/hosts-mysql-mgr-deployment/resources/mysqld.service

# 分别启动
sudo systemctl daemon-reload
sudo systemctl enable mysqld
sudo systemctl restart mysqld
sudo systemctl status mysqld
sudo journalctl -afu mysqld
</pre></code>
</details></p>

<h2 id="6">6. 允许远程 &amp; 改密 <code>(不可复制直接执行, 请根据上一步生成的临时密码来重置密码)</code><a class="headerlink" href="#6" title="Permanent link">&para;</a></h2>
<ul>
<li>
<p>node1、node2、node3 分别执行</p>
</li>
<li>
<p>首次初始化必须修改密码</p>
</li>
</ul>
<div class="highlight"><pre><span></span><code><a id="__codelineno-2-1" name="__codelineno-2-1"></a><a href="#__codelineno-2-1"><span class="linenos" data-linenos="1 "></span></a><span class="nv">$MYSQL_HOME</span>/bin/mysqladmin -u root -p password --socket<span class="o">=</span>/mnt/disk1/mysql/mysqld.sock
</code></pre></div>
<ul>
<li>本地连接</li>
</ul>
<div class="highlight"><pre><span></span><code><a id="__codelineno-3-1" name="__codelineno-3-1"></a><a href="#__codelineno-3-1"><span class="linenos" data-linenos="1 "></span></a><span class="nv">$MYSQL_HOME</span>/bin/mysql -S <span class="nv">$MYSQL_SOCKET</span> -uroot -p
<a id="__codelineno-3-2" name="__codelineno-3-2"></a><a href="#__codelineno-3-2"><span class="linenos" data-linenos="2 "></span></a>Enter password: &lt;输入初始化生成的临时密码&gt;
</code></pre></div>
<ul>
<li>修改密码</li>
</ul>
<div class="highlight"><pre><span></span><code><a id="__codelineno-4-1" name="__codelineno-4-1"></a><a href="#__codelineno-4-1"><span class="linenos" data-linenos=" 1 "></span></a><span class="c1">-- 修改密码的方式1</span>
<a id="__codelineno-4-2" name="__codelineno-4-2"></a><a href="#__codelineno-4-2"><span class="linenos" data-linenos=" 2 "></span></a><span class="n">mysql</span><span class="o">&gt;</span><span class="w"> </span><span class="k">alter</span><span class="w"> </span><span class="k">user</span><span class="w"> </span><span class="n">root</span><span class="o">@</span><span class="s1">&#39;%&#39;</span><span class="w"> </span><span class="n">identified</span><span class="w"> </span><span class="k">by</span><span class="w"> </span><span class="s1">&#39;123456&#39;</span><span class="p">;</span><span class="w"></span>
<a id="__codelineno-4-3" name="__codelineno-4-3"></a><a href="#__codelineno-4-3"><span class="linenos" data-linenos=" 3 "></span></a><span class="c1">-- 修改密码的方式2 (MySQL 5.7)</span>
<a id="__codelineno-4-4" name="__codelineno-4-4"></a><a href="#__codelineno-4-4"><span class="linenos" data-linenos=" 4 "></span></a><span class="n">mysql</span><span class="o">&gt;</span><span class="w"> </span><span class="k">set</span><span class="w"> </span><span class="n">password</span><span class="w"> </span><span class="k">for</span><span class="w"> </span><span class="n">root</span><span class="o">@</span><span class="s1">&#39;%&#39;</span><span class="o">=</span><span class="n">password</span><span class="p">(</span><span class="s1">&#39;123456&#39;</span><span class="p">);</span><span class="w"></span>
<a id="__codelineno-4-5" name="__codelineno-4-5"></a><a href="#__codelineno-4-5"><span class="linenos" data-linenos=" 5 "></span></a><span class="c1">-- 注：MySQL 8.x 必须显示指定密码插件为 mysql_native_password</span>
<a id="__codelineno-4-6" name="__codelineno-4-6"></a><a href="#__codelineno-4-6"><span class="linenos" data-linenos=" 6 "></span></a><span class="n">mysql</span><span class="o">&gt;</span><span class="w"> </span><span class="k">alter</span><span class="w"> </span><span class="k">user</span><span class="w"> </span><span class="n">root</span><span class="o">@</span><span class="s1">&#39;%&#39;</span><span class="w"> </span><span class="n">identified</span><span class="w"> </span><span class="k">with</span><span class="w"> </span><span class="n">mysql_native_password</span><span class="w"> </span><span class="k">by</span><span class="w"> </span><span class="s1">&#39;123456&#39;</span><span class="p">;</span><span class="w"></span>
<a id="__codelineno-4-7" name="__codelineno-4-7"></a><a href="#__codelineno-4-7"><span class="linenos" data-linenos=" 7 "></span></a><span class="c1">-- 授权允许远程登录方式1</span>
<a id="__codelineno-4-8" name="__codelineno-4-8"></a><a href="#__codelineno-4-8"><span class="linenos" data-linenos=" 8 "></span></a><span class="n">mysql</span><span class="o">&gt;</span><span class="w"> </span><span class="k">grant</span><span class="w"> </span><span class="k">all</span><span class="w"> </span><span class="k">privileges</span><span class="w"> </span><span class="k">on</span><span class="w"> </span><span class="o">*</span><span class="p">.</span><span class="o">*</span><span class="w"> </span><span class="k">to</span><span class="w"> </span><span class="n">root</span><span class="o">@</span><span class="s1">&#39;%&#39;</span><span class="w"> </span><span class="n">identified</span><span class="w"> </span><span class="k">by</span><span class="w"> </span><span class="s1">&#39;123456&#39;</span><span class="p">;</span><span class="w"></span>
<a id="__codelineno-4-9" name="__codelineno-4-9"></a><a href="#__codelineno-4-9"><span class="linenos" data-linenos=" 9 "></span></a><span class="c1">-- 授权允许远程登录方式2</span>
<a id="__codelineno-4-10" name="__codelineno-4-10"></a><a href="#__codelineno-4-10"><span class="linenos" data-linenos="10 "></span></a><span class="n">mysql</span><span class="o">&gt;</span><span class="w"> </span><span class="n">use</span><span class="w"> </span><span class="n">mysql</span><span class="p">;</span><span class="w"> </span><span class="k">update</span><span class="w"> </span><span class="k">user</span><span class="w"> </span><span class="k">set</span><span class="w"> </span><span class="k">host</span><span class="o">=</span><span class="s1">&#39;%&#39;</span><span class="w"> </span><span class="k">where</span><span class="w"> </span><span class="k">user</span><span class="o">=</span><span class="s1">&#39;root&#39;</span><span class="w"> </span><span class="k">and</span><span class="w"> </span><span class="k">host</span><span class="o">=</span><span class="s1">&#39;localhost&#39;</span><span class="p">;</span><span class="w"></span>
<a id="__codelineno-4-11" name="__codelineno-4-11"></a><a href="#__codelineno-4-11"><span class="linenos" data-linenos="11 "></span></a><span class="c1">-- 刷新权限</span>
<a id="__codelineno-4-12" name="__codelineno-4-12"></a><a href="#__codelineno-4-12"><span class="linenos" data-linenos="12 "></span></a><span class="n">mysql</span><span class="o">&gt;</span><span class="w"> </span><span class="n">flush</span><span class="w"> </span><span class="k">privileges</span><span class="p">;</span><span class="w"></span>
</code></pre></div>
<ul>
<li>用户管理 <code>(使用 root 新增 user 和 schema 然后授权)</code></li>
</ul>
<div class="highlight"><pre><span></span><code><a id="__codelineno-5-1" name="__codelineno-5-1"></a><a href="#__codelineno-5-1"><span class="linenos" data-linenos="1 "></span></a><span class="c1">-- 创建用户及数据库.</span>
<a id="__codelineno-5-2" name="__codelineno-5-2"></a><a href="#__codelineno-5-2"><span class="linenos" data-linenos="2 "></span></a><span class="n">mysql</span><span class="o">&gt;</span><span class="w"> </span><span class="k">create</span><span class="w"> </span><span class="k">schema</span><span class="w"> </span><span class="n">testdb</span><span class="p">;</span><span class="w"></span>
<a id="__codelineno-5-3" name="__codelineno-5-3"></a><a href="#__codelineno-5-3"><span class="linenos" data-linenos="3 "></span></a><span class="n">mysql</span><span class="o">&gt;</span><span class="w"> </span><span class="k">create</span><span class="w"> </span><span class="k">user</span><span class="w"> </span><span class="n">test</span><span class="o">@</span><span class="s1">&#39;%&#39;</span><span class="w"> </span><span class="n">identified</span><span class="w"> </span><span class="k">by</span><span class="w"> </span><span class="s1">&#39;123456&#39;</span><span class="p">;</span><span class="w"></span>
<a id="__codelineno-5-4" name="__codelineno-5-4"></a><a href="#__codelineno-5-4"><span class="linenos" data-linenos="4 "></span></a>
<a id="__codelineno-5-5" name="__codelineno-5-5"></a><a href="#__codelineno-5-5"><span class="linenos" data-linenos="5 "></span></a><span class="c1">-- 【重要】: 必须先给 root 用户授予有执行 grant 命令的权限, 才能授权给其他用户. 否则报错: ERROR 1044 (42000): Access denied for user &#39;root&#39;@&#39;%&#39; to database &#39;testdb&#39;</span>
<a id="__codelineno-5-6" name="__codelineno-5-6"></a><a href="#__codelineno-5-6"><span class="linenos" data-linenos="6 "></span></a>
<a id="__codelineno-5-7" name="__codelineno-5-7"></a><a href="#__codelineno-5-7"><span class="linenos" data-linenos="7 "></span></a><span class="n">mysql</span><span class="o">&gt;</span><span class="w"> </span><span class="k">update</span><span class="w"> </span><span class="n">mysql</span><span class="p">.</span><span class="k">user</span><span class="w"> </span><span class="k">set</span><span class="w"> </span><span class="n">Grant_priv</span><span class="o">=</span><span class="s1">&#39;Y&#39;</span><span class="p">,</span><span class="n">Super_priv</span><span class="o">=</span><span class="s1">&#39;Y&#39;</span><span class="w"> </span><span class="k">where</span><span class="w"> </span><span class="k">user</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">&#39;root&#39;</span><span class="w"> </span><span class="k">and</span><span class="w"> </span><span class="k">host</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">&#39;%&#39;</span><span class="p">;</span><span class="w"></span>
<a id="__codelineno-5-8" name="__codelineno-5-8"></a><a href="#__codelineno-5-8"><span class="linenos" data-linenos="8 "></span></a><span class="n">flush</span><span class="w"> </span><span class="k">privileges</span><span class="p">;</span><span class="w"></span>
</code></pre></div>
<div class="highlight"><pre><span></span><code><a id="__codelineno-6-1" name="__codelineno-6-1"></a><a href="#__codelineno-6-1"><span class="linenos" data-linenos="1 "></span></a><span class="c1"># 还必须重启才行.</span>
<a id="__codelineno-6-2" name="__codelineno-6-2"></a><a href="#__codelineno-6-2"><span class="linenos" data-linenos="2 "></span></a>sudo systemctl restart mysqld
<a id="__codelineno-6-3" name="__codelineno-6-3"></a><a href="#__codelineno-6-3"><span class="linenos" data-linenos="3 "></span></a><span class="c1"># 重新以 root 登录</span>
<a id="__codelineno-6-4" name="__codelineno-6-4"></a><a href="#__codelineno-6-4"><span class="linenos" data-linenos="4 "></span></a><span class="nv">$MYSQL_HOME</span>/bin/mysql -S <span class="nv">$MYSQL_SOCKET</span> -uroot -p<span class="s1">&#39;123456&#39;</span>
</code></pre></div>
<div class="highlight"><pre><span></span><code><a id="__codelineno-7-1" name="__codelineno-7-1"></a><a href="#__codelineno-7-1"><span class="linenos" data-linenos="1 "></span></a><span class="c1">-- 重新授权给其他用户.</span>
<a id="__codelineno-7-2" name="__codelineno-7-2"></a><a href="#__codelineno-7-2"><span class="linenos" data-linenos="2 "></span></a><span class="n">mysql</span><span class="o">&gt;</span><span class="w"> </span><span class="k">grant</span><span class="w"> </span><span class="k">all</span><span class="w"> </span><span class="k">privileges</span><span class="w"> </span><span class="k">on</span><span class="w"> </span><span class="n">testdb</span><span class="p">.</span><span class="o">*</span><span class="w"> </span><span class="k">to</span><span class="w"> </span><span class="s1">&#39;test&#39;</span><span class="o">@</span><span class="s1">&#39;%&#39;</span><span class="w"> </span><span class="n">identified</span><span class="w"> </span><span class="k">by</span><span class="w"> </span><span class="s1">&#39;123456&#39;</span><span class="w"> </span><span class="k">with</span><span class="w"> </span><span class="k">grant</span><span class="w"> </span><span class="k">option</span><span class="p">;</span><span class="w"></span>
</code></pre></div>
<ul>
<li>重登验证</li>
</ul>
<div class="highlight"><pre><span></span><code><a id="__codelineno-8-1" name="__codelineno-8-1"></a><a href="#__codelineno-8-1"><span class="linenos" data-linenos="1 "></span></a><span class="nv">$MYSQL_HOME</span>/bin/mysql -h10.0.0.161 -utest -p<span class="s1">&#39;123456&#39;</span>
</code></pre></div>
<div class="highlight"><pre><span></span><code><a id="__codelineno-9-1" name="__codelineno-9-1"></a><a href="#__codelineno-9-1"><span class="linenos" data-linenos="1 "></span></a><span class="c1">-- 显示权限范围内的数据库</span>
<a id="__codelineno-9-2" name="__codelineno-9-2"></a><a href="#__codelineno-9-2"><span class="linenos" data-linenos="2 "></span></a><span class="n">mysql</span><span class="o">&gt;</span><span class="w"> </span><span class="k">show</span><span class="w"> </span><span class="n">databases</span><span class="p">;</span><span class="w"></span>
<a id="__codelineno-9-3" name="__codelineno-9-3"></a><a href="#__codelineno-9-3"><span class="linenos" data-linenos="3 "></span></a><span class="o">+</span><span class="c1">--------------------+</span>
<a id="__codelineno-9-4" name="__codelineno-9-4"></a><a href="#__codelineno-9-4"><span class="linenos" data-linenos="4 "></span></a><span class="o">|</span><span class="w"> </span><span class="k">Database</span><span class="w">           </span><span class="o">|</span><span class="w"></span>
<a id="__codelineno-9-5" name="__codelineno-9-5"></a><a href="#__codelineno-9-5"><span class="linenos" data-linenos="5 "></span></a><span class="o">+</span><span class="c1">--------------------+</span>
<a id="__codelineno-9-6" name="__codelineno-9-6"></a><a href="#__codelineno-9-6"><span class="linenos" data-linenos="6 "></span></a><span class="o">|</span><span class="w"> </span><span class="n">information_schema</span><span class="w"> </span><span class="o">|</span><span class="w"></span>
<a id="__codelineno-9-7" name="__codelineno-9-7"></a><a href="#__codelineno-9-7"><span class="linenos" data-linenos="7 "></span></a><span class="o">|</span><span class="w"> </span><span class="n">testdb</span><span class="w">             </span><span class="o">|</span><span class="w"></span>
<a id="__codelineno-9-8" name="__codelineno-9-8"></a><a href="#__codelineno-9-8"><span class="linenos" data-linenos="8 "></span></a><span class="o">+</span><span class="c1">--------------------+</span>
<a id="__codelineno-9-9" name="__codelineno-9-9"></a><a href="#__codelineno-9-9"><span class="linenos" data-linenos="9 "></span></a><span class="mi">2</span><span class="w"> </span><span class="k">rows</span><span class="w"> </span><span class="k">in</span><span class="w"> </span><span class="k">set</span><span class="w"> </span><span class="p">(</span><span class="mi">0</span><span class="p">.</span><span class="mi">00</span><span class="w"> </span><span class="n">sec</span><span class="p">)</span><span class="w"></span>
</code></pre></div>
<h2 id="7-mgr">7. 配置 MGR<a class="headerlink" href="#7-mgr" title="Permanent link">&para;</a></h2>
<ul>
<li>
<p><a href="https://dev.mysql.com/doc/refman/5.7/en/group-replication.html">官方文档: dev.mysql.com/doc/refman/5.7/en/group-replication.html</a></p>
</li>
<li>
<p>或使用工具 <a href="https://gitee.com/wl4g/blogs/raw/master/docs/articles/database/docker-mysql-mgr-simple-deployment/resources/mgrctl.sh">mgrctl</a> 操作</p>
</li>
</ul>
<div class="highlight"><pre><span></span><code><a id="__codelineno-10-1" name="__codelineno-10-1"></a><a href="#__codelineno-10-1"><span class="linenos" data-linenos="1 "></span></a>sudo curl -sSL -o /bin/mgrctl https://gitee.com/wl4g/blogs/raw/master/docs/articles/database/docker-mysql-mgr-simple-deployment/resources/mgrctl.sh
</code></pre></div>
<h3 id="71-node1">7.1 node1(首先启动，引导节点)<a class="headerlink" href="#71-node1" title="Permanent link">&para;</a></h3>
<p><details style="cursor:pointer;;padding-left:2%">
<summary style="color:#007acc;">[展开] Initializating primary nodes</summary>
<code><pre>
-- 安装(mysql8+默认未安装)
INSTALL PLUGIN group_replication SONAME 'group_replication.so';

-- 重置复制配置(可选,如需重置上次创建的配置)
RESET MASTER;
RESET SLAVE ALL;

-- 关闭超级用户只读(MGR启动后会被自动设置从节点为只读)
SET GLOBAL SUPER_READ_ONLY = OFF;
SET GLOBAL READ_ONLY = OFF;

-- 配置账户使其他node登录进来
SET SQL_LOG_BIN=0;
GRANT REPLICATION SLAVE ON *.* TO repl@'%'  IDENTIFIED BY '123456';
SET SQL_LOG_BIN=1;

-- 使用账户密码登录其他node
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';

-- 只需node1需要执行
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;
-- STOP GROUP_REPLICATION;

-- 查看集群成员状态
select * from performance_schema.replication_group_members;
</pre></code>
</details></p>

<h3 id="72-node2node3">7.2. node2、node3(成员节点)<a class="headerlink" href="#72-node2node3" title="Permanent link">&para;</a></h3>
<p><details style="cursor:pointer;;padding-left:2%">
<summary style="color:#007acc;">[展开] Initializating standby nodes</summary>
<code><pre>
-- 安装(mysql8+默认未安装)
INSTALL PLUGIN group_replication SONAME 'group_replication.so';

-- 重置复制配置(可选,如需重置上次创建的配置)
RESET MASTER;
RESET SLAVE ALL;

-- 关闭超级用户只读(MGR启动后会被自动设置从节点为只读)
SET GLOBAL SUPER_READ_ONLY = OFF;
SET GLOBAL READ_ONLY = OFF;

-- 配置账户使其他node登录进来
SET SQL_LOG_BIN=0;
GRANT REPLICATION SLAVE ON *.* TO repl@'%'  IDENTIFIED BY '123456';
SET SQL_LOG_BIN=1;

-- 使用账户密码登录其他node
CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='123456' FOR CHANNEL 'group_replication_recovery';  

-- 除node1外其他需执行此配置
SET GLOBAL group_replication_allow_local_disjoint_gtids_join=ON; # 高版本已过时
SET GLOBAL group_replication_bootstrap_group=OFF;
START GROUP_REPLICATION;
-- STOP GROUP_REPLICATION;

-- 开启超级用户只读(有时MGR启动后没有会被自动设置从节点为只读？)
SET GLOBAL SUPER_READ_ONLY = ON;
SET GLOBAL READ_ONLY = ON;

-- 查看集群成员状态
select * from performance_schema.replication_group_members;
</pre></code>
</details></p>

<h3 id="73">7.3. 查询主从状态<a class="headerlink" href="#73" title="Permanent link">&para;</a></h3>
<div class="highlight"><pre><span></span><code><a id="__codelineno-11-1" name="__codelineno-11-1"></a><a href="#__codelineno-11-1"><span class="linenos" data-linenos=" 1 "></span></a><span class="k">SELECT</span><span class="w"></span>
<a id="__codelineno-11-2" name="__codelineno-11-2"></a><a href="#__codelineno-11-2"><span class="linenos" data-linenos=" 2 "></span></a><span class="w">    </span><span class="n">rgm</span><span class="p">.</span><span class="n">CHANNEL_NAME</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">channelName</span><span class="p">,</span><span class="w"></span>
<a id="__codelineno-11-3" name="__codelineno-11-3"></a><a href="#__codelineno-11-3"><span class="linenos" data-linenos=" 3 "></span></a><span class="w">    </span><span class="n">rgm</span><span class="p">.</span><span class="n">MEMBER_ID</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">nodeId</span><span class="p">,</span><span class="w"></span>
<a id="__codelineno-11-4" name="__codelineno-11-4"></a><a href="#__codelineno-11-4"><span class="linenos" data-linenos=" 4 "></span></a><span class="w">    </span><span class="n">rgm</span><span class="p">.</span><span class="n">MEMBER_HOST</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">nodeHost</span><span class="p">,</span><span class="w"></span>
<a id="__codelineno-11-5" name="__codelineno-11-5"></a><a href="#__codelineno-11-5"><span class="linenos" data-linenos=" 5 "></span></a><span class="w">    </span><span class="n">rgm</span><span class="p">.</span><span class="n">MEMBER_PORT</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">nodePort</span><span class="p">,</span><span class="w"></span>
<a id="__codelineno-11-6" name="__codelineno-11-6"></a><a href="#__codelineno-11-6"><span class="linenos" data-linenos=" 6 "></span></a><span class="w">    </span><span class="n">rgm</span><span class="p">.</span><span class="n">MEMBER_STATE</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">nodeState</span><span class="p">,</span><span class="w"></span>
<a id="__codelineno-11-7" name="__codelineno-11-7"></a><a href="#__codelineno-11-7"><span class="linenos" data-linenos=" 7 "></span></a><span class="w">    </span><span class="o">@@</span><span class="n">read_only</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">readOnly</span><span class="p">,</span><span class="w"></span>
<a id="__codelineno-11-8" name="__codelineno-11-8"></a><a href="#__codelineno-11-8"><span class="linenos" data-linenos=" 8 "></span></a><span class="w">    </span><span class="o">@@</span><span class="n">super_read_only</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">superReadOnly</span><span class="p">,(</span><span class="w"></span>
<a id="__codelineno-11-9" name="__codelineno-11-9"></a><a href="#__codelineno-11-9"><span class="linenos" data-linenos=" 9 "></span></a><span class="w">    </span><span class="k">CASE</span><span class="w"> </span><span class="p">(</span><span class="k">SELECT</span><span class="w"> </span><span class="k">TRIM</span><span class="p">(</span><span class="n">VARIABLE_VALUE</span><span class="p">)</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="o">`</span><span class="n">performance_schema</span><span class="o">`</span><span class="p">.</span><span class="o">`</span><span class="n">global_status</span><span class="o">`</span><span class="w"> </span><span class="k">WHERE</span><span class="w"> </span><span class="n">VARIABLE_NAME</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">&#39;group_replication_primary_member&#39;</span><span class="p">)</span><span class="w"></span>
<a id="__codelineno-11-10" name="__codelineno-11-10"></a><a href="#__codelineno-11-10"><span class="linenos" data-linenos="10 "></span></a><span class="w">      </span><span class="k">WHEN</span><span class="w"> </span><span class="s1">&#39;&#39;</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="s1">&#39;UNKOWN&#39;</span><span class="w"></span>
<a id="__codelineno-11-11" name="__codelineno-11-11"></a><a href="#__codelineno-11-11"><span class="linenos" data-linenos="11 "></span></a><span class="w">      </span><span class="k">WHEN</span><span class="w"> </span><span class="n">rgm</span><span class="p">.</span><span class="n">MEMBER_ID</span><span class="w"> </span><span class="k">THEN</span><span class="w"> </span><span class="s1">&#39;PRIMARY&#39;</span><span class="w"></span>
<a id="__codelineno-11-12" name="__codelineno-11-12"></a><a href="#__codelineno-11-12"><span class="linenos" data-linenos="12 "></span></a><span class="w">      </span><span class="k">ELSE</span><span class="w"> </span><span class="s1">&#39;STANDBY&#39;</span><span class="w"> </span><span class="k">END</span><span class="w"></span>
<a id="__codelineno-11-13" name="__codelineno-11-13"></a><a href="#__codelineno-11-13"><span class="linenos" data-linenos="13 "></span></a><span class="w">    </span><span class="p">)</span><span class="w"> </span><span class="k">AS</span><span class="w"> </span><span class="n">nodeRole</span><span class="w"></span>
<a id="__codelineno-11-14" name="__codelineno-11-14"></a><a href="#__codelineno-11-14"><span class="linenos" data-linenos="14 "></span></a><span class="k">FROM</span><span class="w"></span>
<a id="__codelineno-11-15" name="__codelineno-11-15"></a><a href="#__codelineno-11-15"><span class="linenos" data-linenos="15 "></span></a><span class="w">    </span><span class="o">`</span><span class="n">performance_schema</span><span class="o">`</span><span class="p">.</span><span class="o">`</span><span class="n">replication_group_members</span><span class="o">`</span><span class="w"> </span><span class="n">rgm</span><span class="w"></span>
</code></pre></div>
<ul>
<li>结果输出</li>
</ul>
<div class="highlight"><pre><span></span><code><a id="__codelineno-12-1" name="__codelineno-12-1"></a><a href="#__codelineno-12-1"><span class="linenos" data-linenos="1 "></span></a>+---------------------------+--------------------------------------+------------------+----------+-----------+----------+---------------+----------+
<a id="__codelineno-12-2" name="__codelineno-12-2"></a><a href="#__codelineno-12-2"><span class="linenos" data-linenos="2 "></span></a>| channelName               | nodeId                               | nodeHost         | nodePort | nodeState | readOnly | superReadOnly | nodeRole |
<a id="__codelineno-12-3" name="__codelineno-12-3"></a><a href="#__codelineno-12-3"><span class="linenos" data-linenos="3 "></span></a>+---------------------------+--------------------------------------+------------------+----------+-----------+----------+---------------+----------+
<a id="__codelineno-12-4" name="__codelineno-12-4"></a><a href="#__codelineno-12-4"><span class="linenos" data-linenos="4 "></span></a>| group_replication_applier | 290cfb2f-4123-11ec-a209-00163e0bdf1e | node1 |    13306 | ONLINE    |        1 |             1 | STANDBY  |
<a id="__codelineno-12-5" name="__codelineno-12-5"></a><a href="#__codelineno-12-5"><span class="linenos" data-linenos="5 "></span></a>| group_replication_applier | 7a9dd78b-4122-11ec-ac63-00163e0c24a7 | node2 |    13306 | ONLINE    |        1 |             1 | STANDBY  |
<a id="__codelineno-12-6" name="__codelineno-12-6"></a><a href="#__codelineno-12-6"><span class="linenos" data-linenos="6 "></span></a>| group_replication_applier | df6b5139-4121-11ec-bb72-00163e11baa2 | node3 |    13306 | ONLINE    |        1 |             1 | PRIMARY  |
<a id="__codelineno-12-7" name="__codelineno-12-7"></a><a href="#__codelineno-12-7"><span class="linenos" data-linenos="7 "></span></a>+---------------------------+--------------------------------------+------------------+----------+-----------+----------+---------------+----------+
<a id="__codelineno-12-8" name="__codelineno-12-8"></a><a href="#__codelineno-12-8"><span class="linenos" data-linenos="8 "></span></a>3 rows in set (0.00 sec)
</code></pre></div>
<h3 id="74">7.4. 其他相关指令<a class="headerlink" href="#74" title="Permanent link">&para;</a></h3>
<div class="highlight"><pre><span></span><code><a id="__codelineno-13-1" name="__codelineno-13-1"></a><a href="#__codelineno-13-1"><span class="linenos" data-linenos=" 1 "></span></a><span class="k">SET</span><span class="w"> </span><span class="k">GLOBAL</span><span class="w"> </span><span class="n">read_only</span><span class="o">=</span><span class="mi">0</span><span class="p">;</span><span class="w"> </span><span class="c1">-- 1:启动/0:关闭，如：多主模式式，当stop group_replication时会自动将此节点设置为ON，启动后会自动设为OFF</span>
<a id="__codelineno-13-2" name="__codelineno-13-2"></a><a href="#__codelineno-13-2"><span class="linenos" data-linenos=" 2 "></span></a><span class="n">FLUSH</span><span class="w"> </span><span class="k">PRIVILEGES</span><span class="p">;</span><span class="w"></span>
<a id="__codelineno-13-3" name="__codelineno-13-3"></a><a href="#__codelineno-13-3"><span class="linenos" data-linenos=" 3 "></span></a><span class="k">SHOW</span><span class="w"> </span><span class="n">VARIABLES</span><span class="w"> </span><span class="k">like</span><span class="w"> </span><span class="s1">&#39;read_only&#39;</span><span class="p">;</span><span class="w"></span>
<a id="__codelineno-13-4" name="__codelineno-13-4"></a><a href="#__codelineno-13-4"><span class="linenos" data-linenos=" 4 "></span></a><span class="k">SHOW</span><span class="w"> </span><span class="n">VARIABLES</span><span class="w"> </span><span class="k">like</span><span class="w"> </span><span class="s1">&#39;%slow_query_log%&#39;</span><span class="p">;</span><span class="w"></span>
<a id="__codelineno-13-5" name="__codelineno-13-5"></a><a href="#__codelineno-13-5"><span class="linenos" data-linenos=" 5 "></span></a><span class="k">SHOW</span><span class="w"> </span><span class="n">VARIABLES</span><span class="w"> </span><span class="k">like</span><span class="w"> </span><span class="s1">&#39;%long_query_time%&#39;</span><span class="p">;</span><span class="w"></span>
<a id="__codelineno-13-6" name="__codelineno-13-6"></a><a href="#__codelineno-13-6"><span class="linenos" data-linenos=" 6 "></span></a><span class="k">SHOW</span><span class="w"> </span><span class="n">VARIABLES</span><span class="w"> </span><span class="k">like</span><span class="w"> </span><span class="s1">&#39;%log_queries_not_using_indexes%&#39;</span><span class="p">;</span><span class="w"></span>
<a id="__codelineno-13-7" name="__codelineno-13-7"></a><a href="#__codelineno-13-7"><span class="linenos" data-linenos=" 7 "></span></a>
<a id="__codelineno-13-8" name="__codelineno-13-8"></a><a href="#__codelineno-13-8"><span class="linenos" data-linenos=" 8 "></span></a><span class="c1">-- 慢SQL统计</span>
<a id="__codelineno-13-9" name="__codelineno-13-9"></a><a href="#__codelineno-13-9"><span class="linenos" data-linenos=" 9 "></span></a><span class="k">SET</span><span class="w"> </span><span class="k">GLOBAL</span><span class="w"> </span><span class="n">slow_query_log</span><span class="o">=</span><span class="k">on</span><span class="p">;</span><span class="w"></span>
<a id="__codelineno-13-10" name="__codelineno-13-10"></a><a href="#__codelineno-13-10"><span class="linenos" data-linenos="10 "></span></a><span class="k">SET</span><span class="w"> </span><span class="k">GLOBAL</span><span class="w"> </span><span class="n">long_query_time</span><span class="o">=</span><span class="mi">0</span><span class="p">.</span><span class="mi">1</span><span class="p">;</span><span class="w"></span>
<a id="__codelineno-13-11" name="__codelineno-13-11"></a><a href="#__codelineno-13-11"><span class="linenos" data-linenos="11 "></span></a><span class="k">SET</span><span class="w"> </span><span class="k">GLOBAL</span><span class="w"> </span><span class="n">log_queries_not_using_indexes</span><span class="o">=</span><span class="mi">0</span><span class="p">;</span><span class="w"></span>
</code></pre></div>
<h2 id="8">8. 数据备份<a class="headerlink" href="#8" title="Permanent link">&para;</a></h2>
<blockquote>
<p>注: 以下脚本中数据库地址账号密码等，需要自行修改。</p>
</blockquote>
<ul>
<li>创建备份账号</li>
</ul>
<div class="highlight"><pre><span></span><code><a id="__codelineno-14-1" name="__codelineno-14-1"></a><a href="#__codelineno-14-1"><span class="linenos" data-linenos="1 "></span></a><span class="k">create</span><span class="w"> </span><span class="k">user</span><span class="w"> </span><span class="o">`</span><span class="n">wordpress_backup</span><span class="o">`@`</span><span class="mi">127</span><span class="p">.</span><span class="mi">0</span><span class="p">.</span><span class="mi">0</span><span class="p">.</span><span class="mi">1</span><span class="o">`</span><span class="w"> </span><span class="n">identified</span><span class="w"> </span><span class="k">with</span><span class="w"> </span><span class="n">mysql_native_password</span><span class="w"> </span><span class="k">by</span><span class="w"> </span><span class="s1">&#39;123456&#39;</span><span class="p">;</span><span class="w"></span>
<a id="__codelineno-14-2" name="__codelineno-14-2"></a><a href="#__codelineno-14-2"><span class="linenos" data-linenos="2 "></span></a><span class="k">grant</span><span class="w"> </span><span class="k">Lock</span><span class="w"> </span><span class="n">Tables</span><span class="p">,</span><span class="w"> </span><span class="k">Select</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="o">`</span><span class="n">wordpress</span><span class="o">`</span><span class="p">.</span><span class="o">*</span><span class="w"> </span><span class="k">TO</span><span class="w"> </span><span class="o">`</span><span class="n">wordpress_backup</span><span class="o">`@`</span><span class="mi">127</span><span class="p">.</span><span class="mi">0</span><span class="p">.</span><span class="mi">0</span><span class="p">.</span><span class="mi">1</span><span class="o">`</span><span class="p">;</span><span class="w"></span>
<a id="__codelineno-14-3" name="__codelineno-14-3"></a><a href="#__codelineno-14-3"><span class="linenos" data-linenos="3 "></span></a><span class="k">grant</span><span class="w"> </span><span class="n">Reload</span><span class="w"> </span><span class="k">ON</span><span class="w"> </span><span class="o">*</span><span class="p">.</span><span class="o">*</span><span class="w"> </span><span class="k">TO</span><span class="w"> </span><span class="o">`</span><span class="n">wordpress_backup</span><span class="o">`@`</span><span class="mi">127</span><span class="p">.</span><span class="mi">0</span><span class="p">.</span><span class="mi">0</span><span class="p">.</span><span class="mi">1</span><span class="o">`</span><span class="p">;</span><span class="w"></span>
</code></pre></div>
<p><details style="cursor:pointer;;padding-left:2%">
<summary style="color:#007acc;">[展开] /usr/lib/mysql-current/bin/mysql-backup.sh</summary>
<code><pre>
# 加载环境变量
. /etc/profile

# 下载备份脚本(最新)
sudo curl -sSL -o $MYSQL_HOME/bin/mysql-backup.sh https://gitee.com/wl4g/blogs/raw/master/docs/articles/database/hosts-mysql-mgr-deployment/resources/mysql-backup.sh

# 或从此处下载(可能延迟)
curl -sSL -o $MYSQL_HOME/bin/mysql-backup.sh http://pkg.wl4g.com/software/mysql/script-tools/mysql-backup.sh
sudo chmod +x ${MYSQL_HOME}/bin/mysql-backup.sh

# 加入 crontab 任务
sudo echo "0  *  *  *  * root  /bin/bash ${MYSQL_HOME}/bin/mysql-backup.sh -f" >> /etc/crontab
sudo systemctl restart crond
tail -f /var/log/messages
</pre></code>
</details></p>

<h2 id="9-faq">9. FAQ<a class="headerlink" href="#9-faq" title="Permanent link">&para;</a></h2>
<h3 id="91-start-group_replication-mgr">9.1 初始执行 <code>start group_replication;</code> 启动 MGR 插件失败<a class="headerlink" href="#91-start-group_replication-mgr" title="Permanent link">&para;</a></h3>
<div class="highlight"><pre><span></span><code><a id="__codelineno-15-1" name="__codelineno-15-1"></a><a href="#__codelineno-15-1"><span class="linenos" data-linenos="1 "></span></a>...
<a id="__codelineno-15-2" name="__codelineno-15-2"></a><a href="#__codelineno-15-2"><span class="linenos" data-linenos="2 "></span></a>2021-04-15T12:55:23.019059Z 3 [ERROR] Plugin group_replication reported: &#39;[GCS] Invalid hostname or IP address ( &quot;127.0.0.1:3306&quot;) assigned to the parameter local_node!&#39;
<a id="__codelineno-15-3" name="__codelineno-15-3"></a><a href="#__codelineno-15-3"><span class="linenos" data-linenos="3 "></span></a>2021-04-15T12:55:23.019091Z 3 [ERROR] Plugin group_replication reported: &#39;Unable to initialize the group communication engine&#39;
<a id="__codelineno-15-4" name="__codelineno-15-4"></a><a href="#__codelineno-15-4"><span class="linenos" data-linenos="4 "></span></a>2021-04-15T12:55:23.019102Z 3 [ERROR] Plugin group_replication reported: &#39;Error on group communication engine initialization&#39;
<a id="__codelineno-15-5" name="__codelineno-15-5"></a><a href="#__codelineno-15-5"><span class="linenos" data-linenos="5 "></span></a>...
</code></pre></div>
<ul>
<li>启动 MGR 失败，从日志看是 <code>loose-group_replication_group_seeds</code> 和 <code>loose-group_replication_local_address</code> 相关配置有问题... 最终确认原因是 MySQL5.7 读取配置存在 bug，<a href="https://bugs.mysql.com/bug.php?id=98524">请参考『 MySQL 读取 my.cnf 配置键值'='号左右边空格 bug』</a>，建议最好不要空格最靠谱.</li>
</ul>
<hr />
<h3 id="92-start-group_replication-mgr">9.2 运行期间，由于主机故障或误操作等错误情况，导致部分节点故障，且恢复执行 <code>start group_replication;</code> 启动 MGR 插件失败<a class="headerlink" href="#92-start-group_replication-mgr" title="Permanent link">&para;</a></h3>
<div class="highlight"><pre><span></span><code><a id="__codelineno-16-1" name="__codelineno-16-1"></a><a href="#__codelineno-16-1"><span class="linenos" data-linenos=" 1 "></span></a>...
<a id="__codelineno-16-2" name="__codelineno-16-2"></a><a href="#__codelineno-16-2"><span class="linenos" data-linenos=" 2 "></span></a>2021-04-17T13:39:57.759602Z 121 [ERROR] Slave SQL for channel &#39;group_replication_applier&#39;: Could not execute Update_rows event on table aabbcc.t_test; Can&#39;t find record in &#39;t_test&#39;, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND, Error_code: 1032
<a id="__codelineno-16-3" name="__codelineno-16-3"></a><a href="#__codelineno-16-3"><span class="linenos" data-linenos=" 3 "></span></a>2021-04-17T13:39:57.759627Z 121 [Warning] Slave: Can&#39;t find record in &#39;t_test&#39; Error_code: 1032
<a id="__codelineno-16-4" name="__codelineno-16-4"></a><a href="#__codelineno-16-4"><span class="linenos" data-linenos=" 4 "></span></a>2021-04-17T13:39:57.759641Z 121 [ERROR] Plugin group_replication reported: &#39;The applier thread execution was aborted. Unable to process more transactions, this member will now leave the group.&#39;
<a id="__codelineno-16-5" name="__codelineno-16-5"></a><a href="#__codelineno-16-5"><span class="linenos" data-linenos=" 5 "></span></a>2021-04-17T13:39:57.759662Z 121 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with &quot;SLAVE START&quot;. We stopped at log &#39;FIRST&#39; position 0.
<a id="__codelineno-16-6" name="__codelineno-16-6"></a><a href="#__codelineno-16-6"><span class="linenos" data-linenos=" 6 "></span></a>2021-04-17T13:39:57.759674Z 118 [ERROR] Plugin group_replication reported: &#39;Fatal error during execution on the Applier process of Group Replication. The server will now leave the group.&#39;
<a id="__codelineno-16-7" name="__codelineno-16-7"></a><a href="#__codelineno-16-7"><span class="linenos" data-linenos=" 7 "></span></a>2021-04-17T13:39:57.759727Z 118 [ERROR] Plugin group_replication reported: &#39;[GCS] The member is already leaving or joining a group.&#39;
<a id="__codelineno-16-8" name="__codelineno-16-8"></a><a href="#__codelineno-16-8"><span class="linenos" data-linenos=" 8 "></span></a>2021-04-17T13:39:57.759757Z 118 [ERROR] Plugin group_replication reported: &#39;Unable to confirm whether the server has left the group or not. Check performance_schema.replication_group_members to check group membership information.&#39;
<a id="__codelineno-16-9" name="__codelineno-16-9"></a><a href="#__codelineno-16-9"><span class="linenos" data-linenos=" 9 "></span></a>2021-04-17T13:39:57.759775Z 118 [Note] Plugin group_replication reported: &#39;Going to wait for view modification&#39;
<a id="__codelineno-16-10" name="__codelineno-16-10"></a><a href="#__codelineno-16-10"><span class="linenos" data-linenos="10 "></span></a>2021-04-17T13:39:58.760466Z 0 [ERROR] Plugin group_replication reported: &#39;There was a previous plugin error while the member joined the group. The member will now exit the group.&#39;
<a id="__codelineno-16-11" name="__codelineno-16-11"></a><a href="#__codelineno-16-11"><span class="linenos" data-linenos="11 "></span></a>...
</code></pre></div>
<p>从日志分析 <code>Can't find record in 't_test', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND, Error_code: 1032</code> 说明主主(主从)之间复制存在不一致问题，解决供参考：</p>
<div class="highlight"><pre><span></span><code><a id="__codelineno-17-1" name="__codelineno-17-1"></a><a href="#__codelineno-17-1"><span class="linenos" data-linenos="1 "></span></a><span class="n">STOP</span><span class="w"> </span><span class="n">GROUP_REPLICATION</span><span class="p">;</span><span class="w"> </span><span class="c1">-- 停止出现问题的节点MGR</span>
<a id="__codelineno-17-2" name="__codelineno-17-2"></a><a href="#__codelineno-17-2"><span class="linenos" data-linenos="2 "></span></a><span class="k">RESET</span><span class="w"> </span><span class="n">SLAVE</span><span class="w"> </span><span class="k">ALL</span><span class="w"> </span><span class="k">FOR</span><span class="w"> </span><span class="n">CHANNEL</span><span class="w"> </span><span class="ss">&quot;group_replication_applier&quot;</span><span class="p">;</span><span class="w"> </span><span class="c1">-- 重置复制通道（logbin/relaylog不一致）</span>
<a id="__codelineno-17-3" name="__codelineno-17-3"></a><a href="#__codelineno-17-3"><span class="linenos" data-linenos="3 "></span></a><span class="k">START</span><span class="w"> </span><span class="n">GROUP_REPLICATION</span><span class="p">;</span><span class="w"> </span><span class="c1">-- 重启MGR</span>
</code></pre></div>
<p>若还是不行，则尝试执行停止 <code>stop group_replication</code> 所有节点 MGR 复制，然后执行重置 <code>RESET SLAVE ALL FOR CHANNEL "group_replication_applier"</code> 再次逐个启动 <code>start group_replication</code>   - 注：此操作需谨慎使用，操作前请先中断所有业务系统 jdbc 连接，且对各个节点数据备份。</p>
<blockquote>
<p>注：已下线的节点 MGR 插件会自动设置 read_only=ON，千万不要手动 read_only=OFF 然后修改任何数据，否则就算后续恢复正常后，被强制修改的记录将无法被更新，报错 <code>3101 - Plugin instructed the server to rollback the current transaction.</code></p>
</blockquote>
<hr />
<h3 id="93-master-mgr-recovering">9.3 从 Master 依次启动 MGR，从节点一直是 <code>RECOVERING</code> 状态<a class="headerlink" href="#93-master-mgr-recovering" title="Permanent link">&para;</a></h3>
<div class="highlight"><pre><span></span><code><a id="__codelineno-18-1" name="__codelineno-18-1"></a><a href="#__codelineno-18-1"><span class="linenos" data-linenos="1 "></span></a>[ERROR] Slave SQL for channel&#39;group_replication_recovery&#39;: Error &#39;Operation CREATE USER failed for&#39;piriineos&#39;@&#39;localhost&#39;&#39; on query. Default database: &#39;&#39;. Query: &#39;CREATE USER&#39;piriineos&#39;@&#39;localhost&#39; IDENTIFIED WITH &#39;mysql_native_password&#39; AS&#39;*077BFD72D9E814194FBA9A90A6A8DB13BC476718&#39;&#39;, Error_code: 1396
</code></pre></div>
<blockquote>
<p>slave apply 了 master 操作系统表的事务，这里是 master 上创建用户的语句，忘记 <code>set sql_log_bin=0;</code> 了，不想让 slave 执行的操作都需要设置它，这里的解决办法是直接把这个事务的 gtid 放到 purged 表中，跳过他即可。</p>
</blockquote>
<p><details style="cursor:pointer;;padding-left:2%">
<summary style="color:#007acc;">[展开] Resetting Group Replication</summary>
<code><pre>
-- 重新创建 MGR 主从复制，先停止所有节点
STOP GROUP_REPLICATION; -- 注意执行顺序，先停止从节点最后停止主节点。启动顺序则相反，是先启动主节点
-- 重置主节点
-- 作用：
-- 删除binlog索引文件中列出的所有binlog文件;
-- 清空binlog索引文件;
-- 创建一个新的binlog文件;
-- 清空系统变量gtid_purged和gtid_executed ;
-- 在MySQL 5.7.5 及后续版本中, RESET MASTER还会会清空 [mysql.gtid_executed](https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-concepts.html#replication-gtids-gtid-executed-table) 数据表; 
RESET MASTER;

-- 重置从节点
-- 作用：
-- 清除slave 复制时的master binlog的位置;
-- 清空master info, relay log info ;
-- 删除所有的relay log文件，并创建一个新的relay log文件;
-- 重置复制延迟(change master to 的 MASTER_DELAY 参数指定的)为 0;
-- 另外，不会改变gtid_executed or gtid_purged ，也不会改变复制连接使用的参数，例如 master host, master port, master user, or master password ;
-- 如果是执行 RESET SLAVE ALL 则需重新执行 change master to 指定复制连接参数;
RESET SLAVE;

-- 然后重复 7.1 和 7.2，重启所有节点 MGR
</pre></code>
</details></p>

<h3 id="94-start-grouplication-error-on-opening-a-connection-to-1921682133061-on-local-port-33061">9.4 <code>START GROUPLICATION</code> 启动报错日志: <code>Error on opening a connection to 192.168.2.1:33061 on local port: 33061.’</code><a class="headerlink" href="#94-start-grouplication-error-on-opening-a-connection-to-1921682133061-on-local-port-33061" title="Permanent link">&para;</a></h3>
<p><a href="https://forums.percona.com/t/mysql-group-replication-error-on-opening-a-connection-to-192-168-2-1-33061-on-local-port-33061/8087">https://forums.percona.com/t/mysql-group-replication-error-on-opening-a-connection-to-192-168-2-1-33061-on-local-port-33061/8087</a></p>
<div class="highlight"><pre><span></span><code><a id="__codelineno-19-1" name="__codelineno-19-1"></a><a href="#__codelineno-19-1"><span class="linenos" data-linenos="1 "></span></a>mysql&gt; START GROUP_REPLICATION;
<a id="__codelineno-19-2" name="__codelineno-19-2"></a><a href="#__codelineno-19-2"><span class="linenos" data-linenos="2 "></span></a>
<a id="__codelineno-19-3" name="__codelineno-19-3"></a><a href="#__codelineno-19-3"><span class="linenos" data-linenos="3 "></span></a>[ERROR] Plugin group_replication reported: ‘[GCS] Error on opening a connection to 192.168.2.1:33061 on local port: 33061.’
<a id="__codelineno-19-4" name="__codelineno-19-4"></a><a href="#__codelineno-19-4"><span class="linenos" data-linenos="4 "></span></a>[ERROR] Plugin group_replication reported: &#39;[GCS] Error on opening a connection to 192.168.2.2:33061 on local port: 33061.
<a id="__codelineno-19-5" name="__codelineno-19-5"></a><a href="#__codelineno-19-5"><span class="linenos" data-linenos="5 "></span></a>&#39;[ERROR] Plugin group_replication reported: ‘[GCS] Error connecting to all peers. Member join failed. Local port: 33061’
<a id="__codelineno-19-6" name="__codelineno-19-6"></a><a href="#__codelineno-19-6"><span class="linenos" data-linenos="6 "></span></a>[Warning] Plugin group_replication reported: ‘read failed’
<a id="__codelineno-19-7" name="__codelineno-19-7"></a><a href="#__codelineno-19-7"><span class="linenos" data-linenos="7 "></span></a>Error reading relay log event for channel ‘group_replication_applier’: slave SQL thread was killed
</code></pre></div>
<p>引导节点可能设置正确，重新设置重启 Group Replication
<div class="highlight"><pre><span></span><code><a id="__codelineno-20-1" name="__codelineno-20-1"></a><a href="#__codelineno-20-1"><span class="linenos" data-linenos="1 "></span></a><span class="k">SET</span><span class="w"> </span><span class="k">GLOBAL</span><span class="w"> </span><span class="n">group_replication_bootstrap_group</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="k">ON</span><span class="p">;</span><span class="w"></span>
<a id="__codelineno-20-2" name="__codelineno-20-2"></a><a href="#__codelineno-20-2"><span class="linenos" data-linenos="2 "></span></a><span class="k">START</span><span class="w"> </span><span class="n">GROUP_REPLICATION</span><span class="p">;</span><span class="w"></span>
<a id="__codelineno-20-3" name="__codelineno-20-3"></a><a href="#__codelineno-20-3"><span class="linenos" data-linenos="3 "></span></a><span class="k">SET</span><span class="w"> </span><span class="k">GLOBAL</span><span class="w"> </span><span class="n">group_replication_bootstrap_group</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="k">OFF</span><span class="p">;</span><span class="w"></span>
</code></pre></div></p>
<h3 id="95-mysqld-innodb-mmap136019968-bytes-failed-errno-12">9.5 启动 mysqld 错误 <code>InnoDB: mmap(136019968 bytes) failed; errno 12</code><a class="headerlink" href="#95-mysqld-innodb-mmap136019968-bytes-failed-errno-12" title="Permanent link">&para;</a></h3>
<div class="highlight"><pre><span></span><code><a id="__codelineno-21-1" name="__codelineno-21-1"></a><a href="#__codelineno-21-1"><span class="linenos" data-linenos="1 "></span></a>&gt; tail -99f /mnt/disk1/log/mysql/mysqld.err
<a id="__codelineno-21-2" name="__codelineno-21-2"></a><a href="#__codelineno-21-2"><span class="linenos" data-linenos="2 "></span></a>InnoDB: mmap(136019968 bytes) failed; errno 12
</code></pre></div>
<ul>
<li><a href="https://www.jianshu.com/p/590f93216f21">参考: 由于禁用 swap 引起的 mysqld 启动失败</a></li>
</ul>
<h3 id="96-mysqld-mysqldservice-main-process-exited-codekilled-status9kill">9.6 启动 mysqld 错误 <code>mysqld.service: Main process exited, code=killed, status=9/KILL</code><a class="headerlink" href="#96-mysqld-mysqldservice-main-process-exited-codekilled-status9kill" title="Permanent link">&para;</a></h3>
<ul>
<li>日志</li>
</ul>
<div class="highlight"><pre><span></span><code><a id="__codelineno-22-1" name="__codelineno-22-1"></a><a href="#__codelineno-22-1"><span class="linenos" data-linenos=" 1 "></span></a>&gt; journalctl -u mysqld.service -f
<a id="__codelineno-22-2" name="__codelineno-22-2"></a><a href="#__codelineno-22-2"><span class="linenos" data-linenos=" 2 "></span></a>mysqld.service: Main process exited, code=killed, status=9/KILL
<a id="__codelineno-22-3" name="__codelineno-22-3"></a><a href="#__codelineno-22-3"><span class="linenos" data-linenos=" 3 "></span></a>Nov 09 10:51:56 k8s-master-node1 systemd[1]: mysqld.service: Failed with result &#39;signal&#39;.
<a id="__codelineno-22-4" name="__codelineno-22-4"></a><a href="#__codelineno-22-4"><span class="linenos" data-linenos=" 4 "></span></a>Nov 09 10:51:56 k8s-master-node1 systemd[1]: mysqld.service: Scheduled restart job, restart counter is at 5.
<a id="__codelineno-22-5" name="__codelineno-22-5"></a><a href="#__codelineno-22-5"><span class="linenos" data-linenos=" 5 "></span></a>Nov 09 10:51:56 k8s-master-node1 systemd[1]: Stopped MySQL Server.
<a id="__codelineno-22-6" name="__codelineno-22-6"></a><a href="#__codelineno-22-6"><span class="linenos" data-linenos=" 6 "></span></a>
<a id="__codelineno-22-7" name="__codelineno-22-7"></a><a href="#__codelineno-22-7"><span class="linenos" data-linenos=" 7 "></span></a>&gt; tail -99f /mnt/disk1/log/mysql/mysqld.err
<a id="__codelineno-22-8" name="__codelineno-22-8"></a><a href="#__codelineno-22-8"><span class="linenos" data-linenos=" 8 "></span></a>...
<a id="__codelineno-22-9" name="__codelineno-22-9"></a><a href="#__codelineno-22-9"><span class="linenos" data-linenos=" 9 "></span></a>2021-11-09T02:58:42.778133Z 0 [Note] InnoDB: Using Linux native AIO
<a id="__codelineno-22-10" name="__codelineno-22-10"></a><a href="#__codelineno-22-10"><span class="linenos" data-linenos="10 "></span></a>2021-11-09T02:58:42.779655Z 0 [Note] InnoDB: Number of pools: 1
<a id="__codelineno-22-11" name="__codelineno-22-11"></a><a href="#__codelineno-22-11"><span class="linenos" data-linenos="11 "></span></a>2021-11-09T02:58:42.779802Z 0 [Note] InnoDB: Using CPU crc32 instructions
<a id="__codelineno-22-12" name="__codelineno-22-12"></a><a href="#__codelineno-22-12"><span class="linenos" data-linenos="12 "></span></a>2021-11-09T02:58:42.782165Z 0 [Note] InnoDB: Initializing buffer pool, total size = 2G, instances = 8, chunk size = 128M
</code></pre></div>
<ul>
<li>分析</li>
</ul>
<p>从日志看 <code>Initializing buffer pool</code> 就 holding 了，但从 journal 日志看 <code>Main process exited, code=killed, status=9/KILL</code> 是被 kill 掉的，之后又尝试将 <code>/etc/my.cnf</code> 中 <code>innodb_buffer_pool_size</code> 注释掉或者改小为 <code>512M</code> 然后 <code>systemctl restart mysqld.service</code> 发现都启动正常，分析可能是由于初始化 innno buffer memory 触发系统限制被 kill 掉了（此问题环境是 alibaba cloud ubuntu 20.04）</p>
<ul>
<li>解决</li>
</ul>
<p>TODO</p>
<hr />
<h2 id="10-mgr">10. MGR 后期运维细则<a class="headerlink" href="#10-mgr" title="Permanent link">&para;</a></h2>
<ul>
<li>
<p>参考 1 <a href="https://blog.csdn.net/qq_38125183/article/details/80861623?spm=1001.2014.3001.5501">MySQL Group Replicaiton 常见错误</a></p>
</li>
<li>
<p>参考 2 <a href="../mysql-mgr-faq_CN/">细细探究 MySQL Group Replicaiton 配置维护故障处理全集</a></p>
</li>
<li>
<p>参考 3 <a href="https://github.com/Neeky/mysqltools#mysql%E7%BB%84%E5%A4%8D%E5%88%B6">基于 ansible 快速 mysql 环境搭建</a></p>
</li>
<li>
<p>参考 4 <a href="https://dev.mysql.com/doc/refman/5.7/en/group-replication-network-partitioning.html">生产运维之网络分区，人工强制指定成员（最后的补救） - dev.mysql.com/doc/refman/5.7/en/group-replication-network-partitioning.html</a> ，可尝试调整此参数: <a href="https://dev.mysql.com/doc/refman/5.7/en/group-replication-options.html#sysvar_group_replication_unreachable_majority_timeout">group_replication_unreachable_majority_timeout</a></p>
</li>
<li>
<p>参考 5 <a href="https://dev.mysql.com/doc/refman/5.7/en/group-replication-options.html">官方 Group Replication 参数说明: dev.mysql.com/doc/refman/5.7/en/group-replication-options.html</a></p>
</li>
</ul>

              
            </article>
          </div>
        </div>
        
      </main>
      
        <footer class="md-footer">
  
  <div class="md-footer-meta md-typeset">
    <div class="md-footer-meta__inner md-grid">
      <div class="md-copyright">
  
  
    Made with
    <a href="https://squidfunk.github.io/mkdocs-material/" target="_blank" rel="noopener">
      Material for MkDocs
    </a>
  
</div>
      
    </div>
  </div>
</footer>
      
    </div>
    <div class="md-dialog" data-md-component="dialog">
      <div class="md-dialog__inner md-typeset"></div>
    </div>
    <script id="__config" type="application/json">{"base": "../../../..", "features": ["search.suggest", "search.highlight", "navigation.tabs", "navigation.expand", "toc.follow", "toc.integrate"], "search": "../../../../assets/javascripts/workers/search.5e67fbfe.min.js", "translations": {"clipboard.copied": "Copied to clipboard", "clipboard.copy": "Copy to clipboard", "search.config.lang": "en", "search.config.pipeline": "trimmer, stopWordFilter", "search.config.separator": "[\\s\\-]+", "search.placeholder": "Search", "search.result.more.one": "1 more on this page", "search.result.more.other": "# more on this page", "search.result.none": "No matching documents", "search.result.one": "1 matching document", "search.result.other": "# matching documents", "search.result.placeholder": "Type to start searching", "search.result.term.missing": "Missing", "select.version.title": "Select version"}, "version": {"default": "latest", "provider": "mike"}}</script>
    
    
      <script src="../../../../assets/javascripts/bundle.c44cc438.min.js"></script>
      
        <script src="../../../../static/js/util.js"></script>
      
    
  </body>
</html>